<?php

include 'DBconn.php';

class SCSearch {

    public function cancelScItem($VObject) {

        $sc_id = "";
        $sales_type = "";
        $last_sc_det_id = 0;

        $sc_pro_qty = 0;
        $sc_serialnumber_fk = 0;
        $s_result = mysql_query("Select sc_serialnumber_fk,pro_qty,sales_type from sales_conformation_details where detserialnumber='$VObject->sc_detserialnumber_fk'") or die(mysql_error());
        if ($s_row = mysql_fetch_object($s_result)) {
            $sc_pro_qty = (int) $s_row->pro_qty;
            $sc_serialnumber_fk = (int) $s_row->sc_serialnumber_fk;
            $sales_type = (int) $s_row->sales_type;
        }

        $po_detserialnumberArr = array();
        $sc_detserialnumberArr = array();
        $map_serialnumberArr = array();
        $warehouse_serialnumber_fkArr = array();
        $pro_serialnumber_fkArr = array();
        $balance_qtyArr = array();
        $delivered_qtyArr = array();

        $quantity_cancelled = $VObject->cancelled_qty;

        $query = "Select pd.pro_serialnumber_fk,pd.warehouse_serialnumber_fk,pms.map_serialnumber,pms.po_detserialnumber,pms.sc_detserialnumber,pms.allocated_qty-(Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.po_detserialnumber_fk=pms.po_detserialnumber and gmd.sc_detserialnumber_fk=pms.sc_detserialnumber) as balance_qty,
		(Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.po_detserialnumber_fk=pms.po_detserialnumber and gmd.sc_detserialnumber_fk=pms.sc_detserialnumber) as delivered_qty from po_map_sc as pms LEFT JOIN po_details as pd ON pd.activestatus=1 and pd.po_detserialnumber=pms.po_detserialnumber 
		where pms.activestatus=1 and pms.sc_detserialnumber='$VObject->sc_detserialnumber_fk' HAVING balance_qty>0 ORDER BY pms.map_serialnumber DESC";
        $s = mysql_query($query) or die(mysql_error());
        if (mysql_num_rows($s) != 0) {
            while ($row = mysql_fetch_object($s)) {
                $po_detserialnumberArr[] = (int) $row->po_detserialnumber;
                $map_serialnumberArr[] = (int) $row->map_serialnumber;
                $warehouse_serialnumber_fkArr[] = (int) $row->warehouse_serialnumber_fk;
                $pro_serialnumber_fkArr[] = (int) $row->pro_serialnumber_fk;
                $balance_qtyArr[] = (int) $row->balance_qty;
                $delivered_qtyArr[] = (int) $row->delivered_qty;

                $pquery = "Select sc_detserialnumber_fk from po_details where activestatus=1 and po_detserialnumber='$row->po_detserialnumber'";
                $ps = mysql_query($pquery) or die(mysql_error());
                while ($prow = mysql_fetch_object($ps)) {
                    $sc_detserialnumberArr[] = (int) $prow->sc_detserialnumber_fk;
                }
            }

            if ($sales_type == 'Indent Sales') {

                $result = mysql_query("Select sd.sc_serialnumber_fk,sd.pro_serialnumber_fk,sd.pro_qty,sd.base_currency_detserialnumber_fk,cd.currency_value,sd.pro_detserialnumber,prd.currency_serialnumber,prd.markup_type,prd.listprice,prd.discount,prd.markup1,prd.markup2,prd.markup3,prd.markup4,prd.markup5,prd.markup6,prd.default_price 
                from sales_conformation_details as sd LEFT JOIN product_details as prd ON prd.pro_detserialnumber=sd.pro_detserialnumber LEFT JOIN product_header as ph ON ph.serialnumber=sd.pro_serialnumber_fk and ph.activestatus=1 LEFT JOIN currency_detail as cd ON cd.detserialnumber=sd.base_currency_detserialnumber_fk and cd.to_currency_fk=prd.currency_serialnumber 
				where sd.detserialnumber='$VObject->sc_detserialnumber_fk' and sd.sales_type='Indent Sales' and sd.activestatus=1 and sd.detserialnumber IN(Select sc_detserialnumber from po_map_sc where sc_type=3 and activestatus=1)") or die(mysql_error());
                while ($srow = mysql_fetch_object($result)) {
                    $voSc = new VoSalesConformation();
                    $voSc->pro_serialnumber_fk[] = (int) $srow->pro_serialnumber_fk;
                    $voSc->pro_detserialnumber[] = (int) $srow->pro_detserialnumber;
                    $voSc->base_currency_detserialnumber_fk[] = (int) $srow->base_currency_detserialnumber_fk;
                    $voSc->pro_qty[] = $quantity_cancelled;

                    $listprice = (double) $srow->listprice;
                    $discount = (double) $srow->discount;
                    $base_currency_value = (double) $srow->currency_value;
                    $costPrice = round((($listprice - (($discount / 100) * $listprice)) * $base_currency_value), 4);

                    $voSc->price_per_unit[] = $costPrice;
                    $voSc->pro_total_price[] = round(($costPrice * $quantity_cancelled), 4);
                    $voSc->pro_status[] = 'OPEN';
                    $voSc->delivery_loc[] = '';
                    $voSc->sales_type[] = 'Indent Sales';

                    //Header Part
                    $voSc->sc_term = 0;
                    $voSc->sc_gst = 0;
                    $voSc->total_qty = $quantity_cancelled;

                    $cresult = mysql_query("Select serialnumber from currency_header where currency_name='SGD' and activestatus=1") or die(mysql_error());
                    if ($crow = mysql_fetch_object($cresult)) {
                        $voSc->currency_serialnumber_fk = (int) $crow->serialnumber;
                    }

                    $voSc->current_rate = 1;
                    $voSc->sales_con_status = 'CONFIRM';
                    $voSc->sales_con_date = $VObject->cre_datetime;
                    $voSc->no_of_days = 0;
                    $voSc->expected_datetime = $VObject->cre_datetime;
                    $voSc->sc_remarks = '';
                    $voSc->total_amount = round(($costPrice * $quantity_cancelled), 4);
                    $voSc->customer_serialnumber_fk = 0;
                    $voSc->cre_userid_fk = $VObject->cre_userid_fk;
                    $voSc->cre_datetime = $VObject->cre_datetime;
                    $voSc->activestatus = 1;
                    $return_Arr = $this->addNewAescAgainstCancelled($voSc);
                    $sc_id = $return_Arr[0];
                    $last_sc_det_id = (int) $return_Arr[1];
                }
            }

            foreach ($po_detserialnumberArr as $i => $po_detserialnumber) {

                $current_cancelled_qty = 0;

                if ($quantity_cancelled > 0) {

                    $new_free_for_allocation = 0;
                    $new_reserved = 0;
                    $new_on_hand = 0;

                    $query1 = "Select (IFNULL((Select IFNULL(Sum(gd.received_qty-(IFNULL((Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.grn_detserialnumber_fk=gd.grn_detserialnumber),0))),0) from grn_detail as gd where gd.activestatus=1 and gd.po_detserialnumber_fk=smp.po_detserialnumber),0) - (IFNULL((Select IFNULL(Sum(pms.allocated_qty-(IFNULL((Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.sc_detserialnumber_fk=pms.sc_detserialnumber and gmd.po_detserialnumber_fk=pms.po_detserialnumber),0))),0) from po_map_sc as pms where pms.activestatus=1 and pms.po_detserialnumber=smp.po_detserialnumber and pms.self_sc=0 and pms.map_serialnumber<smp.map_serialnumber and pms.sc_detserialnumber IN(Select spms.sc_detserialnumber from po_map_sc as spms where spms.po_detserialnumber=smp.po_detserialnumber and spms.activestatus=1 and spms.self_sc=0 and (spms.sc_type=1 or spms.sc_type=3 or spms.sc_type=2) and spms.map_serialnumber < smp.map_serialnumber)),0))) as on_stock,
					IFNULL((Select IFNULL(Sum(psc.allocated_qty-IFNULL((Select IFNULL(Sum(sgmd.do_pro_qty),0) from gr_do as sgmd where sgmd.activestatus=1 and sgmd.sc_detserialnumber_fk=smp.sc_detserialnumber and sgmd.po_detserialnumber_fk=smp.po_detserialnumber),0)),0) from po_map_sc as psc where psc.activestatus=1 and psc.sc_detserialnumber=smp.sc_detserialnumber and psc.po_detserialnumber=smp.po_detserialnumber),0) as balance_to_deliver,IFNULL((Select IFNULL(Sum(pms.allocated_qty-(IFNULL((Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.sc_detserialnumber_fk=pms.sc_detserialnumber and gmd.po_detserialnumber_fk=pms.po_detserialnumber),0))),0) from po_map_sc as pms where pms.activestatus=1 and pms.po_detserialnumber=smp.po_detserialnumber and pms.self_sc=0 and pms.sc_type!=0 and pms.map_serialnumber>smp.map_serialnumber),0) as post_allocated_qty from po_map_sc as smp where smp.activestatus=1 and smp.sc_detserialnumber='$VObject->sc_detserialnumber_fk' and smp.po_detserialnumber='$po_detserialnumber' GROUP BY smp.sc_detserialnumber LIMIT 1";
                    $s1 = mysql_query($query1) or die(mysql_error());
                    while ($row1 = mysql_fetch_object($s1)) {

                        $on_stock = (int) $row1->on_stock;
                        $balance_to_deliver = (int) $row1->balance_to_deliver;
                        $balance_on_sc = (int) $row1->balance_to_deliver;
                        $post_allocated_qty = (int) $row1->post_allocated_qty;

                        $reserved_qty = 0;

                        if ($on_stock > $balance_to_deliver) {
                            $reserved_qty = $balance_to_deliver;
                        } else if ($on_stock > 0 && $on_stock <= $balance_to_deliver) {
                            $reserved_qty = $on_stock;
                        } else {
                            $reserved_qty = 0;
                        }

                        if ($reserved_qty > 0) {
                            $balance_to_deliver = $balance_to_deliver - $reserved_qty;
                        }

                        if ($balance_to_deliver > $quantity_cancelled) {
                            $new_free_for_allocation = $quantity_cancelled;
                            $current_cancelled_qty = $quantity_cancelled;
                            $quantity_cancelled = 0;
                        } else {
                            $quantity_cancelled = $quantity_cancelled - $balance_to_deliver;
                            $current_cancelled_qty = $balance_to_deliver;
                            $new_free_for_allocation = $balance_to_deliver;
                        }

                        if ($reserved_qty > $quantity_cancelled) {
                            $new_on_hand = $quantity_cancelled;
                            $new_reserved = $quantity_cancelled;
                            $current_cancelled_qty = $current_cancelled_qty + $quantity_cancelled;
                            $quantity_cancelled = 0;
                        } else {
                            $quantity_cancelled = $quantity_cancelled - $reserved_qty;
                            $current_cancelled_qty = $current_cancelled_qty + $reserved_qty;
                            $new_on_hand = $reserved_qty;
                            $new_reserved = $reserved_qty;
                        }

                        //Remove Post Allocated Quantity That On Reserved
						$balnce_on_stock = 0;
						
						if($on_stock > $balance_on_sc){
							$balnce_on_stock = $on_stock - $balance_on_sc;
						}
						
                        $post_allocated_qty = $post_allocated_qty - $balnce_on_stock;

                        if ($post_allocated_qty > 0) {
                            if ($new_on_hand > $post_allocated_qty) {
                                $new_on_hand = $new_on_hand - $post_allocated_qty;
                                $new_reserved = $new_reserved - $post_allocated_qty;
                                $new_free_for_allocation = $new_free_for_allocation + $post_allocated_qty;
                            } else {
                                $new_free_for_allocation = $new_free_for_allocation + $new_on_hand;
                                $new_on_hand = 0;
                                $new_reserved = 0;
                            }
                        }
                    }

                    $s2 = mysql_query("Insert Into cancelled_sc(map_serialnumber_fk,sc_detserialnumber_fk,po_detserialnumber_fk,cancelled_qty,cre_userid_fk,cre_datetime,activestatus) values('$map_serialnumberArr[$i]','$VObject->sc_detserialnumber_fk','$po_detserialnumber','$current_cancelled_qty','$VObject->cre_userid_fk','$VObject->cre_datetime',1)") or die(mysql_error());
                    $last_id = mysql_insert_id();
                    $s3 = mysql_query("Insert Into stock_details(warehouse_serialnumber_fk,trans_detserialnumber,trans_type,pro_serialnumber,on_hand,on_sale,in_order,sold,free_for_allocation,activestatus) values('$warehouse_serialnumber_fkArr[$i]','$last_id','6','$pro_serialnumber_fkArr[$i]','$new_on_hand','$new_reserved',0,0,'$new_free_for_allocation',1)") or die(mysql_error());

                    $s4 = mysql_query("Select on_hand,on_sale,free_for_allocation from stock_header where pro_serialnumber='$pro_serialnumber_fkArr[$i]' and warehouse_serialnumber_fk='$warehouse_serialnumber_fkArr[$i]'") or die(mysql_error());
                    while ($row4 = mysql_fetch_object($s4)) {
                        $on_hand = (int) $row4->on_hand;
                        $on_sale = (int) $row4->on_sale;
                        $free_for_allocation = (int) $row4->free_for_allocation;

                        $on_hand = $on_hand + $new_on_hand;
                        $on_sale = $on_sale - $new_reserved;
                        $free_for_allocation = $free_for_allocation + $new_free_for_allocation;

                        $s5 = mysql_query("Update stock_header set on_hand='$on_hand',on_sale='$on_sale',free_for_allocation='$free_for_allocation' where pro_serialnumber='$pro_serialnumber_fkArr[$i]' and warehouse_serialnumber_fk='$warehouse_serialnumber_fkArr[$i]'") or die(mysql_error());
                    }

                    $s6 = mysql_query("Update po_map_sc set activestatus=IF((allocated_qty-$current_cancelled_qty) > 0,1,0),allocated_qty=allocated_qty-$current_cancelled_qty where map_serialnumber='$map_serialnumberArr[$i]'") or die(mysql_error());

                    if ($last_sc_det_id != 0) {
                        mysql_query("Insert Into po_map_sc(po_detserialnumber,sc_detserialnumber,allocated_qty,sc_type,self_sc,cre_userid_fk,cre_datetime,activestatus) values('$po_detserialnumber','$last_sc_det_id','$current_cancelled_qty',0,1,'$VObject->cre_userid_fk','$VObject->cre_datetime',1)") or die(mysql_error());
                    } else {
						$self_sc_detserialnumber_fk = 0;
						$sc_result = mysql_query("Select sc_detserialnumber_fk from po_details where activestatus=1 and po_detserialnumber='$po_detserialnumber'") or die(mysql_error());						
						while($sc_row = mysql_fetch_object($sc_result)){
							$self_sc_detserialnumber_fk = (int)$sc_row->sc_detserialnumber_fk;
						}
                        $map_result = mysql_query("Update po_map_sc set allocated_qty=allocated_qty+$current_cancelled_qty where sc_type=0 and self_sc=1 and activestatus=1 and po_detserialnumber='$po_detserialnumber' and sc_detserialnumber='$self_sc_detserialnumber_fk'") or die(mysql_error());
						if(mysql_affected_rows()){
							$map_result = mysql_query("Insert Into po_map_sc(po_detserialnumber,sc_detserialnumber,allocated_qty,sc_type,self_sc,cre_userid_fk,cre_datetime,activestatus) values($po_detserialnumber,$self_sc_detserialnumber_fk,$current_cancelled_qty,0,1,'$VObject->cre_userid_fk','$VObject->cre_datetime',1)") or die(mysql_error());
						}
                        mysql_query("Update sales_conformation_details set activestatus=1,pro_qty=pro_qty+$current_cancelled_qty where detserialnumber='$sc_detserialnumberArr[$i]'") or die(mysql_error());

                        $product_array = $this->checkSCProductStatus($sc_detserialnumberArr[$i]);
                        $Squery3 = "update sales_conformation_details  set pro_status='$product_array[1]' where detserialnumber='$sc_detserialnumberArr[$i]' and activestatus=1";
                        mysql_query($Squery3) or die(mysql_error());

                        $result_sc = mysql_query("Select sc_serialnumber_fk from sales_conformation_details where detserialnumber='$sc_detserialnumberArr[$i]'") or die(mysql_error());
                        while ($row_sc = mysql_fetch_object($result_sc)) {

                            //Update Contents On Self SC Table
                            $sc_total_price = 0;
                            $sc_total_qty = 0;
                            $result7 = mysql_query("Select * from sales_conformation_details where sc_serialnumber_fk='$row_sc->sc_serialnumber_fk' and activestatus=1") or die(mysql_error());
                            while ($row7 = mysql_fetch_object($result7)) {
                                $pro_total_price = round(($row7->price_per_unit * $row7->pro_qty), 4);
                                $sc_total_price = $sc_total_price + $pro_total_price;
                                $sc_total_qty = $sc_total_qty + (int) $row7->pro_qty;
                                mysql_query("Update sales_conformation_details set pro_total_price='$pro_total_price' where detserialnumber='$row7->detserialnumber'") or die(mysql_error());
                            }
                            $sc_total_price = round($sc_total_price, 4);
                            mysql_query("Update sales_conformation_header set total_amount='$sc_total_price',total_qty='$sc_total_qty' where serialnumber='$row_sc->sc_serialnumber_fk'") or die(mysql_error());
                            $this->checkSCStatus($row_sc->sc_serialnumber_fk);
                        }
                    }
                } else {
                    break;
                }
            }

            if ($VObject->cancelled_qty < $sc_pro_qty) {
                $quantity = $sc_pro_qty - $VObject->cancelled_qty;
                mysql_query("Update sales_conformation_details set pro_qty='$quantity' where detserialnumber='$VObject->sc_detserialnumber_fk'") or die(mysql_error());
                mysql_query("Update delivery_order_pro_details set sc_qty='$quantity' where sc_detserialnumber_fk='$VObject->sc_detserialnumber_fk'") or die(mysql_error());
            } else if ($sc_pro_qty == $VObject->cancelled_qty) {
                mysql_query("Update sales_conformation_details set activestatus=0 where detserialnumber='$VObject->sc_detserialnumber_fk'") or die(mysql_error());
            }

            if ($last_sc_det_id != 0 && $sales_type == 'Indent Sales') {
                mysql_query("Update po_details set sc_detserialnumber_fk='$last_sc_det_id' where sc_detserialnumber_fk='$VObject->sc_detserialnumber_fk' and po_detserialnumber='$po_detserialnumber' and activestatus=1") or die(mysql_error());
            }

            $product_array = $this->checkSCProductStatus($VObject->sc_detserialnumber_fk);
            $Squery3 = "update sales_conformation_details  set pro_status='$product_array[1]' where detserialnumber='$VObject->sc_detserialnumber_fk' and activestatus=1";
            mysql_query($Squery3) or die(mysql_error());
        }

        $query6 = "Select detserialnumber from sales_conformation_details where sc_serialnumber_fk='$sc_serialnumber_fk' and activestatus=1";
        $result6 = mysql_query($query6) or die(mysql_error());
        $num_rows = mysql_num_rows($result6);
        if ($num_rows == 0) {
            mysql_query("Update sales_conformation_header set sales_con_status='VOID' where serialnumber='$sc_serialnumber_fk' and activestatus=1") or die(mysql_error());
        } else {
            $this->checkSCStatus($sc_serialnumber_fk);
        }

        //Update Contents On Table
        $sc_total_price = 0;
        $sc_total_qty = 0;
        $result7 = mysql_query("Select * from sales_conformation_details where sc_serialnumber_fk='$sc_serialnumber_fk' and activestatus=1") or die(mysql_error());
        while ($row7 = mysql_fetch_object($result7)) {
            $pro_total_price = round(($row7->price_per_unit * $row7->pro_qty), 4);
            $sc_total_price = $sc_total_price + $pro_total_price;
            $sc_total_qty = $sc_total_qty + (int) $row7->pro_qty;
            mysql_query("Update sales_conformation_details set pro_total_price='$pro_total_price' where detserialnumber='$row7->detserialnumber'") or die(mysql_error());
        }
        $sc_total_price = round($sc_total_price, 4);
        mysql_query("Update sales_conformation_header set total_amount='$sc_total_price',total_qty='$sc_total_qty' where serialnumber='$sc_serialnumber_fk'") or die(mysql_error());

        $return_mess = "";
        if ($sc_id != "") {
            $return_mess = 'The selected item was cancelled and ' . $sc_id . ' is created';
        } else {
            $return_mess = 'The selected item was cancelled';
        }

        return $return_mess;
    }

   public function cancel_ISC_And_PO($VObject) {
   
        $created_sc_ids = "";
        $sales_type = "";
        $last_sc_det_id = 0;

        $sc_pro_qty = 0;
        $sc_serialnumber_fk = 0;
        $s_result = mysql_query("Select sc_serialnumber_fk,pro_qty,sales_type from sales_conformation_details where detserialnumber='$VObject->sc_detserialnumber_fk'") or die(mysql_error());
        if ($s_row = mysql_fetch_object($s_result)) {
            $sc_pro_qty = (int) $s_row->pro_qty;
            $sc_serialnumber_fk = (int) $s_row->sc_serialnumber_fk;
            $sales_type = (int) $s_row->sales_type;
        }

        $po_detserialnumberArr = array();
        $sc_detserialnumberArr = array();
        $map_serialnumberArr = array();
        $warehouse_serialnumber_fkArr = array();
        $pro_serialnumber_fkArr = array();
        $balance_qtyArr = array();
        $delivered_qtyArr = array();

        $quantity_cancelled = $VObject->cancelled_qty;

        $query = "Select pd.pro_serialnumber_fk,pd.warehouse_serialnumber_fk,pms.map_serialnumber,pms.po_detserialnumber,pms.sc_detserialnumber,pms.allocated_qty-(Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.po_detserialnumber_fk=pms.po_detserialnumber and gmd.sc_detserialnumber_fk=pms.sc_detserialnumber) as balance_qty,
		(Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.po_detserialnumber_fk=pms.po_detserialnumber and gmd.sc_detserialnumber_fk=pms.sc_detserialnumber) as delivered_qty from po_map_sc as pms LEFT JOIN po_details as pd ON pd.activestatus=1 and pd.po_detserialnumber=pms.po_detserialnumber 
		where pms.activestatus=1 and pms.sc_detserialnumber='$VObject->sc_detserialnumber_fk' HAVING balance_qty>0 ORDER BY pms.map_serialnumber DESC";
        $s = mysql_query($query) or die(mysql_error());
        if (mysql_num_rows($s) != 0) {
            while ($row = mysql_fetch_object($s)) {
                $po_detserialnumberArr[] = (int) $row->po_detserialnumber;
                $map_serialnumberArr[] = (int) $row->map_serialnumber;
                $warehouse_serialnumber_fkArr[] = (int) $row->warehouse_serialnumber_fk;
                $pro_serialnumber_fkArr[] = (int) $row->pro_serialnumber_fk;
                $balance_qtyArr[] = (int) $row->balance_qty;
                $delivered_qtyArr[] = (int) $row->delivered_qty;

                $pquery = "Select sc_detserialnumber_fk from po_details where activestatus=1 and po_detserialnumber='$row->po_detserialnumber'";
                $ps = mysql_query($pquery) or die(mysql_error());
                while ($prow = mysql_fetch_object($ps)) {
                    $sc_detserialnumberArr[] = (int) $prow->sc_detserialnumber_fk;
                }
            }           

            foreach ($po_detserialnumberArr as $i => $po_detserialnumber) {

                $current_cancelled_qty = 0;

                if ($quantity_cancelled > 0) {

					//PO Pre Result
					$presult = mysql_query("Select IFNULL(pms.allocated_qty - IFNULL((Select Sum(gmd.do_pro_qty) from gr_do as gmd where gmd.activestatus=1 and gmd.po_detserialnumber_fk=pms.po_detserialnumber and gmd.sc_detserialnumber_fk=pms.sc_detserialnumber),0),0) as allocated_qty from po_map_sc as pms where pms.po_detserialnumber='$po_detserialnumber' and pms.sc_detserialnumber='$VObject->sc_detserialnumber_fk' and pms.activestatus=1") or die(mysql_error());
					
					//SC Cancel
                    $new_free_for_allocation = 0;
                    $new_reserved = 0;
                    $new_on_hand = 0;

                    $query1 = "Select (IFNULL((Select IFNULL(Sum(gd.received_qty-(IFNULL((Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.grn_detserialnumber_fk=gd.grn_detserialnumber),0))),0) from grn_detail as gd where gd.activestatus=1 and gd.po_detserialnumber_fk=smp.po_detserialnumber),0) - (IFNULL((Select IFNULL(Sum(pms.allocated_qty-(IFNULL((Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.sc_detserialnumber_fk=pms.sc_detserialnumber and gmd.po_detserialnumber_fk=pms.po_detserialnumber),0))),0) from po_map_sc as pms where pms.activestatus=1 and pms.po_detserialnumber=smp.po_detserialnumber and pms.self_sc=0 and pms.map_serialnumber<smp.map_serialnumber and pms.sc_detserialnumber IN(Select spms.sc_detserialnumber from po_map_sc as spms where spms.po_detserialnumber=smp.po_detserialnumber and spms.activestatus=1 and spms.self_sc=0 and (spms.sc_type=1 or spms.sc_type=3 or spms.sc_type=2) and spms.map_serialnumber < smp.map_serialnumber)),0))) as on_stock,
					IFNULL((Select IFNULL(Sum(psc.allocated_qty-IFNULL((Select IFNULL(Sum(sgmd.do_pro_qty),0) from gr_do as sgmd where sgmd.activestatus=1 and sgmd.sc_detserialnumber_fk=smp.sc_detserialnumber and sgmd.po_detserialnumber_fk=smp.po_detserialnumber),0)),0) from po_map_sc as psc where psc.activestatus=1 and psc.sc_detserialnumber=smp.sc_detserialnumber and psc.po_detserialnumber=smp.po_detserialnumber),0) as balance_to_deliver,IFNULL((Select IFNULL(Sum(pms.allocated_qty-(IFNULL((Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.sc_detserialnumber_fk=pms.sc_detserialnumber and gmd.po_detserialnumber_fk=pms.po_detserialnumber),0))),0) from po_map_sc as pms where pms.activestatus=1 and pms.po_detserialnumber=smp.po_detserialnumber and pms.self_sc=0 and pms.sc_type!=0 and pms.map_serialnumber>smp.map_serialnumber),0) as post_allocated_qty from po_map_sc as smp where smp.activestatus=1 and smp.sc_detserialnumber='$VObject->sc_detserialnumber_fk' and smp.po_detserialnumber='$po_detserialnumber' GROUP BY smp.sc_detserialnumber LIMIT 1";
                    $s1 = mysql_query($query1) or die(mysql_error());
                    while ($row1 = mysql_fetch_object($s1)) {

                        $on_stock = (int) $row1->on_stock;
                        $balance_to_deliver = (int) $row1->balance_to_deliver;
                        $balance_on_sc = (int) $row1->balance_to_deliver;
                        $post_allocated_qty = (int) $row1->post_allocated_qty;

                        $reserved_qty = 0;

                        if ($on_stock > $balance_to_deliver) {
                            $reserved_qty = $balance_to_deliver;
                        } else if ($on_stock > 0 && $on_stock <= $balance_to_deliver) {
                            $reserved_qty = $on_stock;
                        } else {
                            $reserved_qty = 0;
                        }

                        if ($reserved_qty > 0) {
                            $balance_to_deliver = $balance_to_deliver - $reserved_qty;
                        }
						
                        if ($balance_to_deliver > $quantity_cancelled) {
                            $new_free_for_allocation = $quantity_cancelled;
                            $current_cancelled_qty = $quantity_cancelled;
                            $quantity_cancelled = 0;
                        } else {
                            $quantity_cancelled = $quantity_cancelled - $balance_to_deliver;
                            $current_cancelled_qty = $balance_to_deliver;
                            $new_free_for_allocation = $balance_to_deliver;
                        }
						
                        if ($reserved_qty > $quantity_cancelled) {
                            $new_on_hand = $quantity_cancelled;
                            $new_reserved = $quantity_cancelled;
                            $current_cancelled_qty = $current_cancelled_qty + $quantity_cancelled;
                            $quantity_cancelled = 0;
                        } else {
                            $quantity_cancelled = $quantity_cancelled - $reserved_qty;
                            $current_cancelled_qty = $current_cancelled_qty + $reserved_qty;
                            $new_on_hand = $reserved_qty;
                            $new_reserved = $reserved_qty;
                        }
						
                        //Remove Post Allocated Quantity That On Reserved
						$balnce_on_stock = 0;
						
						if($on_stock > $balance_on_sc){
							$balnce_on_stock = $on_stock - $balance_on_sc;
						}
						
                        $post_allocated_qty = $post_allocated_qty - $balnce_on_stock;
						
                        if ($post_allocated_qty > 0) {
                            if ($new_on_hand > $post_allocated_qty) {
                                $new_on_hand = $new_on_hand - $post_allocated_qty;
                                $new_reserved = $new_reserved - $post_allocated_qty;
                                $new_free_for_allocation = $new_free_for_allocation + $post_allocated_qty;
                            } else {
                                $new_free_for_allocation = $new_free_for_allocation + $new_on_hand;
                                $new_on_hand = 0;
                                $new_reserved = 0;
                            }
                        }
                    }

                    $s2 = mysql_query("Insert Into cancelled_sc(map_serialnumber_fk,sc_detserialnumber_fk,po_detserialnumber_fk,cancelled_qty,cre_userid_fk,cre_datetime,activestatus) values('$map_serialnumberArr[$i]','$VObject->sc_detserialnumber_fk','$po_detserialnumber','$current_cancelled_qty','$VObject->cre_userid_fk','$VObject->cre_datetime',1)") or die(mysql_error());
                    $last_id = mysql_insert_id();
                    $s3 = mysql_query("Insert Into stock_details(warehouse_serialnumber_fk,trans_detserialnumber,trans_type,pro_serialnumber,on_hand,on_sale,in_order,sold,free_for_allocation,activestatus) values('$warehouse_serialnumber_fkArr[$i]','$last_id','6','$pro_serialnumber_fkArr[$i]','$new_on_hand','$new_reserved',0,0,'$new_free_for_allocation',1)") or die(mysql_error());

                    $s4 = mysql_query("Select on_hand,on_sale,free_for_allocation from stock_header where pro_serialnumber='$pro_serialnumber_fkArr[$i]' and warehouse_serialnumber_fk='$warehouse_serialnumber_fkArr[$i]'") or die(mysql_error());
                    while ($row4 = mysql_fetch_object($s4)) {
                        $on_hand = (int) $row4->on_hand;
                        $on_sale = (int) $row4->on_sale;
						$free_for_allocation = (int) $row4->free_for_allocation;

                        $on_hand = $on_hand + $new_on_hand;
                        $on_sale = $on_sale - $new_reserved;
						$free_for_allocation = $free_for_allocation + $new_free_for_allocation;
						
                        $s5 = mysql_query("Update stock_header set on_hand='$on_hand',on_sale='$on_sale',free_for_allocation='$free_for_allocation' where pro_serialnumber='$pro_serialnumber_fkArr[$i]' and warehouse_serialnumber_fk='$warehouse_serialnumber_fkArr[$i]'") or die(mysql_error());
                    }

                    $s6 = mysql_query("Update po_map_sc set activestatus=IF((allocated_qty-$current_cancelled_qty) > 0,1,0),allocated_qty=allocated_qty-$current_cancelled_qty where map_serialnumber='$map_serialnumberArr[$i]'") or die(mysql_error());
					
					if($new_on_hand > 0 && $sc_detserialnumberArr[$i]==$VObject->sc_detserialnumber_fk){
					
						$result = mysql_query("Select sd.sc_serialnumber_fk,sd.pro_serialnumber_fk,sd.pro_qty,sd.base_currency_detserialnumber_fk,cd.currency_value,sd.pro_detserialnumber,prd.currency_serialnumber,prd.markup_type,prd.listprice,prd.discount,prd.markup1,prd.markup2,prd.markup3,prd.markup4,prd.markup5,prd.markup6,prd.default_price 
						from sales_conformation_details as sd LEFT JOIN product_details as prd ON prd.pro_detserialnumber=sd.pro_detserialnumber LEFT JOIN product_header as ph ON ph.serialnumber=sd.pro_serialnumber_fk and ph.activestatus=1 LEFT JOIN currency_detail as cd ON cd.detserialnumber=sd.base_currency_detserialnumber_fk and cd.to_currency_fk=prd.currency_serialnumber 
						where sd.detserialnumber='$VObject->sc_detserialnumber_fk' and sd.sales_type='Indent Sales' and sd.activestatus=1 and sd.detserialnumber IN(Select sc_detserialnumber from po_map_sc where sc_type=3 and activestatus=1)") or die(mysql_error());
						while ($srow = mysql_fetch_object($result)) {
							$voSc = new VoSalesConformation();
							$voSc->pro_serialnumber_fk[] = (int) $srow->pro_serialnumber_fk;
							$voSc->pro_detserialnumber[] = (int) $srow->pro_detserialnumber;
							$voSc->base_currency_detserialnumber_fk[] = (int) $srow->base_currency_detserialnumber_fk;
							$voSc->pro_qty[] = $new_on_hand;

							$listprice = (double) $srow->listprice;
							$discount = (double) $srow->discount;
							$base_currency_value = (double) $srow->currency_value;
							$costPrice = round((($listprice - (($discount / 100) * $listprice)) * $base_currency_value), 4);

							$voSc->price_per_unit[] = $costPrice;
							$voSc->pro_total_price[] = round(($costPrice * $new_on_hand), 4);
							$voSc->pro_status[] = 'OPEN';
							$voSc->delivery_loc[] = '';
							$voSc->sales_type[] = 'Indent Sales';

							//Header Part
							$voSc->sc_term = 0;
							$voSc->sc_gst = 0;	
							$voSc->total_qty = $new_on_hand;

							$cresult = mysql_query("Select serialnumber from currency_header where currency_name='SGD' and activestatus=1") or die(mysql_error());
							if ($crow = mysql_fetch_object($cresult)) {
								$voSc->currency_serialnumber_fk = (int) $crow->serialnumber;
							}

							$voSc->current_rate = 1;
							$voSc->sales_con_status = 'CONFIRM';
							$voSc->sales_con_date = $VObject->cre_datetime;
							$voSc->no_of_days = 0;
							$voSc->expected_datetime = $VObject->cre_datetime;
							$voSc->sc_remarks = '';
							$voSc->total_amount = round(($costPrice * $new_on_hand), 4);
							$voSc->customer_serialnumber_fk = 0;
							$voSc->cre_userid_fk = $VObject->cre_userid_fk;
							$voSc->cre_datetime = $VObject->cre_datetime;
							$voSc->activestatus = 1;
							$return_Arr = $this->addNewAescAgainstCancelled($voSc);
							$sc_id = $return_Arr[0];
							
							//Concat SC ids
							if($last_sc_det_id!=0){
								$created_sc_ids = $created_sc_ids.", ".$sc_id;
							}else{$created_sc_ids = $sc_id;}
							
							$last_sc_det_id = (int) $return_Arr[1];
						}
						
						mysql_query("Insert Into po_map_sc(po_detserialnumber,sc_detserialnumber,allocated_qty,sc_type,self_sc,cre_userid_fk,cre_datetime,activestatus) values('$po_detserialnumber','$last_sc_det_id','$new_on_hand',0,1,'$VObject->cre_userid_fk','$VObject->cre_datetime',1)") or die(mysql_error());
						mysql_query("Update po_details set sc_detserialnumber_fk='$last_sc_det_id' where sc_detserialnumber_fk='$VObject->sc_detserialnumber_fk' and po_detserialnumber='$po_detserialnumber' and activestatus=1") or die(mysql_error());						
						
					}else if($new_on_hand > 0 && $sc_detserialnumberArr[$i]!=$VObject->sc_detserialnumber_fk){

						mysql_query("Update po_map_sc set allocated_qty=allocated_qty+$new_on_hand where activestatus=1 and self_sc=1 and sc_type=0 and po_detserialnumber='$po_detserialnumber' and sc_detserialnumber='$sc_detserialnumberArr[$i]'") or die(mysql_error());
						mysql_query("Update sales_conformation_details set pro_qty=pro_qty+$new_on_hand where and detserialnumber='$sc_detserialnumberArr[$i]'") or die(mysql_error());
						mysql_query("Update delivery_order_pro_details set sc_qty=sc_qty+$new_on_hand where and sc_detserialnumber_fk='$sc_detserialnumberArr[$i]'") or die(mysql_error());
						
						$product_array = $this->checkSCProductStatus($sc_detserialnumberArr[$i]);
                        $Squery3 = "update sales_conformation_details  set pro_status='$product_array[1]' where detserialnumber='$sc_detserialnumberArr[$i]' and activestatus=1";
                        mysql_query($Squery3) or die(mysql_error());
						
						$result_sc = mysql_query("Select sc_serialnumber_fk from sales_conformation_details where detserialnumber='$sc_detserialnumberArr[$i]'") or die(mysql_error());
                        while ($row_sc = mysql_fetch_object($result_sc)) {

                            //Update Contents On Self SC Table
                            $sc_total_price = 0;
                            $sc_total_qty = 0;
                            $result7 = mysql_query("Select * from sales_conformation_details where sc_serialnumber_fk='$row_sc->sc_serialnumber_fk' and activestatus=1") or die(mysql_error());
                            while ($row7 = mysql_fetch_object($result7)) {
                                $pro_total_price = round(($row7->price_per_unit * $row7->pro_qty), 4);
                                $sc_total_price = $sc_total_price + $pro_total_price;
                                $sc_total_qty = $sc_total_qty + (int) $row7->pro_qty;
                                mysql_query("Update sales_conformation_details set pro_total_price='$pro_total_price' where detserialnumber='$row7->detserialnumber'") or die(mysql_error());
                            }
                            $sc_total_price = round($sc_total_price, 4);
                            mysql_query("Update sales_conformation_header set total_amount='$sc_total_price',total_qty='$sc_total_qty' where serialnumber='$row_sc->sc_serialnumber_fk'") or die(mysql_error());
                            $this->checkSCStatus($row_sc->sc_serialnumber_fk);
                        }
					}
					
					//PO Cancel
					while($prow = mysql_fetch_object($presult)){
					
						$po_allocated_qty = (int)$prow->allocated_qty;
						
						$quantity_cancel_po = $po_allocated_qty - $new_on_hand;
						
						if($quantity_cancel_po >= $new_free_for_allocation){
							mysql_query("Update po_details set activestatus=IF(pro_qty-$new_free_for_allocation>0,1,0),pro_qty=pro_qty-$new_free_for_allocation,pro_total_price=round(((pro_qty-$new_free_for_allocation) * unit_price),4) where po_detserialnumber='$po_detserialnumber'") or die(mysql_error());
							mysql_query("Update grn_detail set po_qty=po_qty-$new_free_for_allocation where po_detserialnumber_fk='$po_detserialnumber' and activestatus=1") or die(mysql_error());
							
							if($new_free_for_allocation > 0){
								$s1 = mysql_query("Insert Into cancelled_po(map_serialnumber_fk,sc_detserialnumber_fk,po_detserialnumber_fk,cancelled_qty,reactiveated,cre_userid_fk,cre_datetime,activestatus) values('$map_serialnumberArr[$i]','$VObject->sc_detserialnumber_fk','$po_detserialnumber','$new_free_for_allocation',1,'$VObject->cre_userid_fk','$VObject->cre_datetime',1)") or die(mysql_error());
								$last_id = mysql_insert_id();
								$s2 = mysql_query("Insert Into stock_details(warehouse_serialnumber_fk,trans_detserialnumber,trans_type,pro_serialnumber,on_hand,on_sale,in_order,sold,free_for_allocation,activestatus) values('$warehouse_serialnumber_fkArr[$i]','$last_id','5','$pro_serialnumber_fkArr[$i]',0,0,'$new_free_for_allocation',0,'$new_free_for_allocation',1)") or die(mysql_error());
								
								$s3 = mysql_query("Select in_order,free_for_allocation from stock_header where pro_serialnumber='$pro_serialnumber_fkArr[$i]' and warehouse_serialnumber_fk='$warehouse_serialnumber_fkArr[$i]'") or die(mysql_error());
								while ($row3 = mysql_fetch_object($s3)) {
									$in_order = (int) $row3->in_order;
									$free_for_allocation = (int) $row3->free_for_allocation;

									$in_order = $in_order - $new_free_for_allocation;
									$free_for_allocation = $free_for_allocation - $new_free_for_allocation;
	
									$s5 = mysql_query("Update stock_header set in_order='$in_order',free_for_allocation='$free_for_allocation' where pro_serialnumber='$pro_serialnumber_fkArr[$i]' and warehouse_serialnumber_fk='$warehouse_serialnumber_fkArr[$i]'") or die(mysql_error());
								}
								
								//Update Po Details
								$product_array = $this->checkPOProductStatus($po_detserialnumber);
								$Squery3 = "Update po_details  set pro_status='$product_array[1]' where po_detserialnumber='$po_detserialnumber' and activestatus=1";
								mysql_query($Squery3) or die(mysql_error());
								
								$query6 = "Select po_serialnumber_fk from po_details where po_detserialnumber='$po_detserialnumber'";
								$result6 = mysql_query($query6) or die(mysql_error());
								while($row6 = mysql_fetch_object($result6)){
								 
									//Update Contents On PO Table
									$po_total_price = 0;
									$po_total_qty = 0;
									$result7 = mysql_query("Select * from po_details where po_serialnumber_fk='$row6->po_serialnumber_fk' and activestatus=1") or die(mysql_error());
									if(mysql_num_rows($result7) > 0) {
									
										while ($row7 = mysql_fetch_object($result7)) {
											$pro_total_price = round(($row7->unit_price * $row7->pro_qty), 4);
											$po_total_price = $po_total_price + $pro_total_price;
											$po_total_qty = $po_total_qty + (int) $row7->pro_qty;
											mysql_query("Update po_details set pro_total_price='$pro_total_price' where po_detserialnumber='$row7->po_detserialnumber'") or die(mysql_error());
										}
										$po_total_price = round($po_total_price, 4);
										mysql_query("Update po_header set total_amount='$po_total_price',total_qty='$po_total_qty' where po_serialnumber='$row6->po_serialnumber_fk'") or die(mysql_error());
										$this->checkPOStatus($row6->po_serialnumber_fk);
									}else{
										mysql_query("Update po_header set po_status='VOID' where po_serialnumber='$row6->po_serialnumber_fk' and activestatus=1") or die(mysql_error());
									}									
								}
							}
							
						}else {
						   break;
						}
					}
					
				}else{
				   break;
				}
            }

			//Update ISC Contents On Table
            if ($VObject->cancelled_qty < $sc_pro_qty) {
                $quantity = $sc_pro_qty - $VObject->cancelled_qty;
                mysql_query("Update sales_conformation_details set pro_qty='$quantity' where detserialnumber='$VObject->sc_detserialnumber_fk'") or die(mysql_error());
                mysql_query("Update delivery_order_pro_details set sc_qty='$quantity' where sc_detserialnumber_fk='$VObject->sc_detserialnumber_fk'") or die(mysql_error());
            } else if ($sc_pro_qty == $VObject->cancelled_qty) {
                mysql_query("Update sales_conformation_details set activestatus=0 where detserialnumber='$VObject->sc_detserialnumber_fk'") or die(mysql_error());
            }

            $product_array = $this->checkSCProductStatus($VObject->sc_detserialnumber_fk);
            $Squery3 = "update sales_conformation_details  set pro_status='$product_array[1]' where detserialnumber='$VObject->sc_detserialnumber_fk' and activestatus=1";
            mysql_query($Squery3) or die(mysql_error());
			
			$query6 = "Select detserialnumber from sales_conformation_details where sc_serialnumber_fk='$sc_serialnumber_fk' and activestatus=1";
			$result6 = mysql_query($query6) or die(mysql_error());
			$num_rows = mysql_num_rows($result6);
			if ($num_rows == 0) {
				mysql_query("Update sales_conformation_header set sales_con_status='VOID' where serialnumber='$sc_serialnumber_fk' and activestatus=1") or die(mysql_error());
			} else {
				$this->checkSCStatus($sc_serialnumber_fk);
			}
        }

        $return_mess = "";
        if ($created_sc_ids != "") {
            $return_mess = 'The selected item was cancelled and ' . $created_sc_ids .' created for the reserved quantity that was cancelled.';
        } else {
            $return_mess = 'The selected item was cancelled';
        }

        return $return_mess;
    }

    public function cancel_Unmapped_Indent_SC($VObject) {

        $sc_pro_qty = 0;
        $sc_serialnumber_fk = 0;
        $s_result = mysql_query("Select sc_serialnumber_fk,pro_qty,sales_type from sales_conformation_details where detserialnumber='$VObject->sc_detserialnumber_fk'") or die(mysql_error());
        if ($s_row = mysql_fetch_object($s_result)) {
            $sc_pro_qty = (int) $s_row->pro_qty;
            $sc_serialnumber_fk = (int) $s_row->sc_serialnumber_fk;
            $sales_type = (int) $s_row->sales_type;
        }

        mysql_query("Update sales_conformation_details set activestatus=IF((pro_qty-$VObject->cancelled_qty)>0,1,0),pro_qty=(pro_qty-$VObject->cancelled_qty),pro_total_price=round(((pro_qty-$VObject->cancelled_qty)*price_per_unit),4) where detserialnumber='$VObject->sc_detserialnumber_fk'") or die(mysql_error());
        mysql_query("Update delivery_order_pro_details set sc_qty=(sc_qty-$VObject->cancelled_qty) where sc_detserialnumber_fk='$VObject->sc_detserialnumber_fk'") or die(mysql_error());
        mysql_query("Insert Into cancelled_sc(map_serialnumber_fk,sc_detserialnumber_fk,po_detserialnumber_fk,cancelled_qty,cre_userid_fk,cre_datetime,activestatus) values('0','$VObject->sc_detserialnumber_fk','0','$VObject->cancelled_qty','$VObject->cre_userid_fk','$VObject->cre_datetime',1)") or die(mysql_error());

        $query6 = "Select detserialnumber from sales_conformation_details where sc_serialnumber_fk='$sc_serialnumber_fk' and activestatus=1";
        $result6 = mysql_query($query6) or die(mysql_error());
        $num_rows = mysql_num_rows($result6);
        if ($num_rows == 0) {
            mysql_query("Update sales_conformation_header set sales_con_status='VOID' where serialnumber='$sc_serialnumber_fk' and activestatus=1") or die(mysql_error());
        } else {
            $this->checkSCStatus($sc_serialnumber_fk);
        }

		//Update Contents On Table
        $sc_total_price = 0;
        $sc_total_qty = 0;
        $result7 = mysql_query("Select * from sales_conformation_details where sc_serialnumber_fk='$sc_serialnumber_fk' and activestatus=1") or die(mysql_error());
        while ($row7 = mysql_fetch_object($result7)) {
            $pro_total_price = round(($row7->price_per_unit * $row7->pro_qty), 4);
            $sc_total_price = $sc_total_price + $pro_total_price;
            $sc_total_qty = $sc_total_qty + (int) $row7->pro_qty;
            mysql_query("Update sales_conformation_details set pro_total_price='$pro_total_price' where detserialnumber='$row7->detserialnumber'") or die(mysql_error());
        }
        $sc_total_price = round($sc_total_price, 4);
        mysql_query("Update sales_conformation_header set total_amount='$sc_total_price',total_qty='$sc_total_qty' where serialnumber='$sc_serialnumber_fk'") or die(mysql_error());

		//Draft-PO Cancel
		mysql_query("Update po_details set activestatus=IF((pro_qty-$VObject->cancelled_qty)>0,1,0),pro_qty=(pro_qty-$VObject->cancelled_qty),pro_total_price=round(((pro_qty-$VObject->cancelled_qty)*unit_price),4) where sc_detserialnumber_fk='$VObject->sc_detserialnumber_fk' and po_serialnumber_fk IN(Select po_serialnumber from po_header where po_status='DRAFT')") or die(mysql_error());
		
		$po_serialnumber_fk = 0;
		$po_res = mysql_query("Select po_serialnumber_fk from po_details where sc_detserialnumber_fk='$VObject->sc_detserialnumber_fk' and po_serialnumber_fk IN(Select po_serialnumber from po_header where po_status='DRAFT')") or die(mysql_error());
        while ($rowp = mysql_fetch_object($po_res)){
			$po_serialnumber_fk = (int)$rowp->po_serialnumber_fk;
		}
		
		//Update Contents On Draft-PO
        $po_total_price = 0;
        $po_total_qty = 0;
        $result7 = mysql_query("Select * from po_details where po_serialnumber_fk='$po_serialnumber_fk' and activestatus=1") or die(mysql_error());
        while ($row7 = mysql_fetch_object($result7)) {
            $pro_total_price = round(($row7->unit_price * $row7->pro_qty), 4);
            $po_total_price = $po_total_price + $pro_total_price;
            $po_total_qty = $po_total_qty + (int) $row7->pro_qty;
            mysql_query("Update po_details set pro_total_price='$pro_total_price' where po_detserialnumber='$row7->po_detserialnumber'") or die(mysql_error());
        }
        $po_total_price = round($po_total_price, 4);
        mysql_query("Update po_header set total_amount='$po_total_price',total_qty='$po_total_qty' where po_serialnumber='$po_serialnumber_fk'") or die(mysql_error());
		
		//Draft-PO Status
		$query6 = "Select po_detserialnumber from po_details where po_serialnumber_fk='$po_serialnumber_fk' and activestatus=1";
        $result6 = mysql_query($query6) or die(mysql_error());
        $num_rows = mysql_num_rows($result6);
        if ($num_rows == 0) {
            mysql_query("Update po_header set po_status='VOID' where po_serialnumber='$po_serialnumber_fk' and activestatus=1") or die(mysql_error());
        }
		
        return 'The selected item was cancelled';
    }

    public function addNewAescAgainstCancelled($VObject) {
        $return_Arr = array();
        $sales_con_code = "";
        $sales_con_code = $this->generateScSelfCode();
        mysql_query("insert into sc_self_code(self_code) values ('$sales_con_code')") or die(mysql_error());
        $return_Arr[] = $sales_con_code;

        $query = "insert into sales_conformation_header(sales_con_code,customer_serialnumber_fk,total_amount,total_qty,sc_term,sc_gst,currency_serialnumber_fk,current_rate,sales_con_status,sales_con_date,no_of_days,expected_datetime,sc_remarks,cre_userid_fk,cre_datetime,activestatus) VALUES ('$sales_con_code','$VObject->customer_serialnumber_fk','$VObject->total_amount','$VObject->total_qty','$VObject->sc_term','$VObject->sc_gst','$VObject->currency_serialnumber_fk','$VObject->current_rate','$VObject->sales_con_status','$VObject->sales_con_date','$VObject->no_of_days','$VObject->expected_datetime','$VObject->sc_remarks','$VObject->cre_userid_fk','$VObject->cre_datetime',1)";
        $s = mysql_query($query) or die(mysql_error());
        if ($s) {
            $last_id = mysql_insert_id();
            foreach ($VObject->pro_serialnumber_fk as $r => $value) {
                $pro_detserialnumber = mysql_real_escape_string($VObject->pro_detserialnumber[$r]);
                $base_currency_detserialnumber_fk = mysql_real_escape_string($VObject->base_currency_detserialnumber_fk[$r]);
                $price_per_unit = mysql_real_escape_string($VObject->price_per_unit[$r]);
                $pro_qty = mysql_real_escape_string($VObject->pro_qty[$r]);
                $pro_total_price = mysql_real_escape_string($VObject->pro_total_price[$r]);
                $pro_status = mysql_real_escape_string($VObject->pro_status[$r]);
                $delivery_loc = mysql_real_escape_string($VObject->delivery_loc[$r]);
                $sales_type = mysql_real_escape_string($VObject->sales_type[$r]);
                $warehouse_serialnumber_fk = 0; //mysql_real_escape_string($VObject->warehouse_serialnumber_fk[$r]);
                $query2 = "insert into sales_conformation_details(sc_serialnumber_fk,pro_serialnumber_fk,pro_detserialnumber,base_currency_detserialnumber_fk,pro_qty,price_per_unit,pro_total_price,pro_status,delivery_loc,warehouse_serialnumber_fk,sales_type,cre_userid_fk,cre_datetime,activestatus) values('$last_id','$value','$pro_detserialnumber','$base_currency_detserialnumber_fk','$pro_qty','$price_per_unit','$pro_total_price','$pro_status','$delivery_loc','$warehouse_serialnumber_fk','$sales_type','$VObject->cre_userid_fk','$VObject->cre_datetime',1)";
                mysql_query($query2) or die(mysql_error());
                $return_Arr[] = mysql_insert_id();
            }
        }

        return $return_Arr;
    }

    public function checkSCStatus($sc_serialnumber) {
        $num_rows = 0;
        $status_rows = 0;
        $status_rows_part = 0;
        $sc_status = "";

        $query = "Select pro_status from sales_conformation_details where sc_serialnumber_fk='$sc_serialnumber' and activestatus=1";
        $s2 = mysql_query($query) or die(mysql_error());
        $num_rows = mysql_num_rows($s2);
        while ($row2 = mysql_fetch_object($s2)) {
            if ($row2->pro_status == "FULLY SATISFIED")
                $status_rows = $status_rows + 1;
            else if ($row2->pro_status == "PARTIALLY SATISFIED")
                $status_rows_part = $status_rows_part + 1;
        }
        if ($num_rows == $status_rows && $status_rows_part == 0)
            $sc_status = "FULLY SATISFIED";
        else if (($status_rows_part > 0 || $status_rows > 0 ) && $num_rows != $status_rows)
            $sc_status = "PARTIALLY SATISFIED";
        else if ($status_rows == 0 && $status_rows_part == 0)
            $sc_status = "CONFIRM";

        $query = "update sales_conformation_header set sales_con_status='$sc_status' where serialnumber='$sc_serialnumber' and activestatus=1";
        $s2 = mysql_query($query) or die(mysql_error());

        return $sc_status;
    }

    public function checkSCProductStatus($sc_detserialnumber_fk) {
        $delivered_qty = 0;
        $pro_status = "";
        $sc_qty = 0;

        $product_array = array();

        $query2 = "select delivered_qty,sc_qty from  delivery_order_pro_details where sc_detserialnumber_fk='$sc_detserialnumber_fk' and activestatus=1";
        $s2 = mysql_query($query2) or die(mysql_error());
        if (mysql_num_rows($s2)) {
            while ($row = mysql_fetch_object($s2)) {
                $delivered_qty+=$row->delivered_qty;
                $sc_qty = $row->sc_qty;
            }

            $product_array[0] = $delivered_qty;

            if ($product_array[0] < $sc_qty)
                $product_array[1] = "PARTIALLY SATISFIED";
            else if ($product_array[0] >= $sc_qty)
                $product_array[1] = "FULLY SATISFIED";
        }
        else {
            $product_array[0] = 0;
            $product_array[1] = "OPEN";
        }
        return $product_array;
    }

    public function checkPOStatus($po_serialnumber) {
        $num_rows = 0;
        $status_rows = 0;
        $status_rows_part = 0;
        $po_status = "";

        $query = "Select pro_status from po_details where po_serialnumber_fk='$po_serialnumber' and activestatus=1";
        $s2 = mysql_query($query) or die(mysql_error());
        $num_rows = mysql_num_rows($s2);
        while ($row2 = mysql_fetch_object($s2)) {
            if ($row2->pro_status == "FULLY SATISFIED")
                $status_rows = $status_rows + 1;
            else if ($row2->pro_status == "PARTIALLY SATISFIED")
                $status_rows_part = $status_rows_part + 1;
        }
        if ($num_rows == $status_rows && $status_rows_part == 0)
            $po_status = "FULLY SATISFIED";
        else if (($status_rows_part > 0 || $status_rows > 0 ) && $num_rows != $status_rows)
            $po_status = "PARTIALLY SATISFIED";
        else if ($status_rows == 0 && $status_rows_part == 0)
            $po_status = "CONFIRM";

        $query2 = "update po_header set po_status='$po_status' where po_serialnumber='$po_serialnumber' and activestatus=1";
        $s2 = mysql_query($query2) or die(mysql_error());

        return $po_status;
    }

    public function checkPOProductStatus($po_detserialnumber_fk) {
        $received_qty = 0;
        $pro_status = "";
        $po_qty = 0;

        $product_array = array();

        $query2 = "Select received_qty,po_qty from grn_detail where po_detserialnumber_fk='$po_detserialnumber_fk'";
        $s2 = mysql_query($query2) or die(mysql_error());
        if (mysql_num_rows($s2) > 0) {
            while ($row = mysql_fetch_object($s2)) {
                $received_qty+=$row->received_qty;
                $po_qty = $row->po_qty;
            }

            $product_array[0] = $received_qty;

            if ($product_array[0] < $po_qty)
                $product_array[1] = "PARTIALLY SATISFIED";
            else if ($product_array[0] >= $po_qty)
                $product_array[1] = "FULLY SATISFIED";
        }else {
            $product_array[0] = 0;
            $product_array[1] = "OPEN";
        }
        return $product_array;
    }

    public function getAllCancelledSc() {
        $ret = array();
        $query = "Select * from cancelled_sc where activestatus=1";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) {
            $temp = new VoCancelledSc();
            $temp->serialnumber = (int) $row->serialnumber;
            $temp->sc_detserialnumber_fk = (int) $row->sc_detserialnumber_fk;
            $temp->cancelled_qty = (int) $row->cancelled_qty;
            $temp->cre_userid_fk = (int) $row->cre_userid_fk;
            $temp->cre_datetime = $row->cre_datetime;
            $temp->activestatus = (boolean) $row->activestatus;
            $ret[] = $temp;
        }
        return $ret;
    }

    public function generateScSelfCode() {
        $res = "";
        $query = "select self_code from sc_self_code order by self_code desc limit 1";
        $result = mysql_query($query) or die(mysql_error());
        if (mysql_num_rows($result)) {
            $row = mysql_fetch_array($result);
            $str = explode("AESC", $row[0]);
            $num = intval($str[1]);
            $res = "AESC" . ++$num;
        } else {
            $res = 'AESC1001';
        }
        return $res;
    }

    public function getCancelledScHeader() {
        $ret = array();
        $query = "Select sh.serialnumber,concat(sh.no_of_days,' Days') as no_of_days,sh.sales_con_code,sh.sales_con_date,sh.sc_remarks,sh.current_rate,sh.sc_gst,cur.currency_name,IFNULL(cus.client_name,'Alliance Enterprices') as customer from sales_conformation_header as sh LEFT JOIN client_header as cus ON cus.serialnumber=sh.customer_serialnumber_fk LEFT JOIN currency_header as cur ON cur.serialnumber=sh.currency_serialnumber_fk where sh.activestatus=1 and 
		sh.serialnumber IN(Select sd.sc_serialnumber_fk from sales_conformation_details as sd where sd.detserialnumber IN(Select scs.sc_detserialnumber_fk from cancelled_sc as scs where scs.activestatus=1))";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) {
            $temp = new VoSCHeaderSearch();
            $temp->sc_serialnumber = (int) $row->serialnumber;
            $temp->sales_con_code = $row->sales_con_code;
            $temp->sales_con_date = date("d-m-Y", strtotime($row->sales_con_date));
            $temp->sales_con_status = "Cancelled";
            $temp->no_of_days = $row->no_of_days;
            $temp->sc_remarks = $row->sc_remarks;
            $temp->customer = $row->customer;
            $temp->currency_name = $row->currency_name;
            $temp->current_rate = (double) $row->current_rate;
            $temp->sc_gst = (double) $row->sc_gst;
            $ret[] = $temp;
        }
        mysql_free_result($s);
        return $ret;
    }

    public function getCancelledScDetails($sc_serialnumber) {
        $ret = array();
        $query = "Select sh.sales_con_code,sd.sc_serialnumber_fk,sd.detserialnumber,sd.pro_serialnumber_fk,pro.pro_name,pro.part_number,pro.pro_description,sd.price_per_unit,IFNULL((Select Sum(cs.cancelled_qty) from cancelled_sc as cs where cs.activestatus=1 and cs.sc_detserialnumber_fk=sd.detserialnumber),0) as pro_qty,round(( IFNULL((Select Sum(cs.cancelled_qty) from cancelled_sc as cs where cs.activestatus=1 and cs.sc_detserialnumber_fk=sd.detserialnumber),0) * sd.price_per_unit),4) as pro_total_price
	   from sales_conformation_details as sd LEFT JOIN sales_conformation_header as sh ON sh.serialnumber=sd.sc_serialnumber_fk LEFT JOIN product_header as pro ON pro.serialnumber=sd.pro_serialnumber_fk where sd.sc_serialnumber_fk='$sc_serialnumber' and sd.detserialnumber IN(Select scs.sc_detserialnumber_fk from cancelled_sc as scs where scs.activestatus=1) HAVING pro_qty>0";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) {
            $temp = new VoSCDetailSearch();
            $temp->sales_con_code = $row->sales_con_code;
            $temp->sales_type = 0;
            $temp->sc_serialnumber_fk = (int) $row->sc_serialnumber_fk;
            $temp->sc_detserialnumber = (int) $row->detserialnumber;
            $temp->pro_serialnumber_fk = (int) $row->pro_serialnumber_fk;
            $temp->pro_qty = (int) $row->pro_qty;
            $temp->delivered_qty = 0;
            $temp->balance_qty = 0;
            $temp->cancelled_qty = 0;
            $temp->price_per_unit = (double) $row->price_per_unit;
            $temp->pro_total_price = (double) round($row->pro_total_price, 4);
            $temp->pro_name = $row->pro_name;
            $temp->part_number = $row->part_number;
            $temp->pro_description = $row->pro_description;
            $temp->reserved_qty = 0;
            $ret[] = $temp;
        }
        mysql_free_result($s);
        return $ret;
    }

    public function getDoLinks($sc_detserialnumber) {
        $ret = array();
        $query = "Select dh.delivery_order_code,dd.delivered_qty,pro.pro_name,pro.part_number,pro.pro_description,ROUND(sd.price_per_unit,4) as price_per_unit,ROUND((sd.price_per_unit * dd.delivered_qty),4) as pro_total_price from delivery_order_pro_details as dd LEFT JOIN sales_conformation_details as sd ON sd.activestatus=1 and sd.detserialnumber=dd.sc_detserialnumber_fk LEFT JOIN delivery_order_header as dh ON dh.activestatus=1 and dh.serialnumber=dd.delivery_order_fk
	   LEFT JOIN product_header as pro ON pro.activestatus=1 and pro.serialnumber=dd.pro_serialnumber_fk where dd.activestatus=1 and dd.sc_detserialnumber_fk='$sc_detserialnumber'";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) {
            $temp = new VoDoLinksSearch();
            $temp->delivery_order_code = $row->delivery_order_code;
            $temp->delivered_qty = (int) $row->delivered_qty;
            $temp->pro_name = $row->pro_name;
            $temp->part_number = $row->part_number;
            $temp->pro_description = $row->pro_description;
            $temp->price_per_unit = (double) $row->price_per_unit;
            $temp->pro_total_price = (double) $row->pro_total_price;
            $ret[] = $temp;
        }
        mysql_free_result($s);
        return $ret;
    }

    public function getRecentSCHeader() {
        $ret = array();
        $query = "Select sh.serialnumber,concat(sh.no_of_days,' Days') as no_of_days,sh.sales_con_code,sh.sales_con_date,sh.sales_con_status,sh.sc_remarks,sh.current_rate,sh.sc_gst,cur.currency_name,IFNULL(cus.client_name,'Alliance Enterprices') as customer from sales_conformation_header as sh LEFT JOIN client_header as cus ON cus.serialnumber=sh.customer_serialnumber_fk LEFT JOIN currency_header as cur ON cur.serialnumber=sh.currency_serialnumber_fk where sh.activestatus=1 and sh.sales_con_status!='DRAFT' and sh.sales_con_status!='VOID' ORDER BY sh.serialnumber DESC limit 20";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) {
            $temp = new VoSCHeaderSearch();
            $temp->sc_serialnumber = (int) $row->serialnumber;
            $temp->sales_con_code = $row->sales_con_code;
            $temp->sales_con_date = date("d-m-Y", strtotime($row->sales_con_date));
            $temp->sales_con_status = $row->sales_con_status;
            $temp->no_of_days = $row->no_of_days;
            $temp->sc_remarks = $row->sc_remarks;
            $temp->customer = $row->customer;
            $temp->currency_name = $row->currency_name;
            $temp->current_rate = (double) $row->current_rate;
            $temp->sc_gst = (double) $row->sc_gst;
            $ret[] = $temp;
        }
        mysql_free_result($s);
        return $ret;
    }

    public function getSCDetails($sc_serialnumber) {
        $ret = array();
        $query = "Select sh.customer_serialnumber_fk,sh.sales_con_code,if(sd.sales_type='Indent Sales' && sh.customer_serialnumber_fk!=0,1,0) as sales_type,sd.sc_serialnumber_fk,sd.detserialnumber,sd.pro_serialnumber_fk,pro.pro_name,pro.part_number,pro.pro_description,sd.pro_qty,sd.price_per_unit,round(( sd.pro_qty * sd.price_per_unit),4) as pro_total_price,IFNULL((Select Sum(dd.delivered_qty) from delivery_order_pro_details as dd where dd.activestatus=1 and dd.sc_detserialnumber_fk=sd.detserialnumber),0) as delivered_qty,(sd.pro_qty - IFNULL((Select Sum(dd.delivered_qty) from delivery_order_pro_details as dd where dd.activestatus=1 and dd.sc_detserialnumber_fk=sd.detserialnumber),0)) as balance_qty
	   from sales_conformation_details as sd LEFT JOIN sales_conformation_header as sh ON sh.serialnumber=sd.sc_serialnumber_fk LEFT JOIN product_header as pro ON pro.serialnumber=sd.pro_serialnumber_fk where sd.activestatus=1 and sd.pro_qty>0 and sd.sc_serialnumber_fk='$sc_serialnumber'";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) {
            $temp = new VoSCDetailSearch();
            $temp->sales_con_code = $row->sales_con_code;
            $temp->sales_type = (int) $row->sales_type;
            $temp->sc_serialnumber_fk = (int) $row->sc_serialnumber_fk;
            $temp->sc_detserialnumber = (int) $row->detserialnumber;
            $temp->pro_serialnumber_fk = (int) $row->pro_serialnumber_fk;
            $temp->pro_name = $row->pro_name;
            $temp->part_number = $row->part_number;
            $temp->pro_description = $row->pro_description;
            $temp->pro_qty = (int) $row->pro_qty;
            $temp->price_per_unit = (double) $row->price_per_unit;
            $temp->pro_total_price = (double) $row->pro_total_price;
            $temp->delivered_qty = (int) $row->delivered_qty;
            $temp->balance_qty = (int) $row->balance_qty;
            $temp->cancelled_qty = (int) $row->balance_qty;

            $balance_to_sale = 0;

            $query2 = "Select pms.map_serialnumber,pms.po_detserialnumber,pms.sc_detserialnumber,IFNULL(Sum(pms.allocated_qty-(Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.po_detserialnumber_fk=pms.po_detserialnumber and gmd.sc_detserialnumber_fk=pms.sc_detserialnumber)),0) as balance_qty,(Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.po_detserialnumber_fk=pms.po_detserialnumber and gmd.sc_detserialnumber_fk=pms.sc_detserialnumber) as delivered_qty from po_map_sc as pms LEFT JOIN po_details as pd ON pd.activestatus=1 and pd.po_detserialnumber=pms.po_detserialnumber where pms.activestatus=1 and pms.sc_detserialnumber='$row->detserialnumber' GROUP BY pms.po_detserialnumber HAVING balance_qty>0 ORDER BY pms.map_serialnumber";
            $s2 = mysql_query($query2) or die(mysql_error());
            while ($row2 = mysql_fetch_object($s2)) {
                $reserved_qty = 0;

                $query3 = "Select (IFNULL((Select IFNULL(Sum(gd.received_qty-(IFNULL((Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.grn_detserialnumber_fk=gd.grn_detserialnumber),0))),0) from grn_detail as gd where gd.activestatus=1 and gd.po_detserialnumber_fk=smp.po_detserialnumber),0) -  IFNULL((Select IFNULL(Sum(pms.allocated_qty-(IFNULL((Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.sc_detserialnumber_fk=pms.sc_detserialnumber and gmd.po_detserialnumber_fk=pms.po_detserialnumber),0))),0) from po_map_sc as pms where pms.activestatus=1 and pms.po_detserialnumber=smp.po_detserialnumber and pms.self_sc=0 and pms.map_serialnumber<smp.map_serialnumber and pms.sc_detserialnumber IN(Select spms.sc_detserialnumber from po_map_sc as spms where spms.po_detserialnumber=smp.po_detserialnumber and spms.activestatus=1 and spms.self_sc=0 and (spms.sc_type=1 or spms.sc_type=3 or(spms.sc_type=2 and spms.map_serialnumber < (Select map_serialnumber from po_map_sc where activestatus=1 
				and self_sc=0 and sc_type=1 and po_detserialnumber=smp.po_detserialnumber and map_serialnumber>spms.map_serialnumber and map_serialnumber<=smp.map_serialnumber LIMIT 1))) and spms.map_serialnumber<smp.map_serialnumber)),0)) as on_stock,IFNULL((Select IFNULL(Sum(psc.allocated_qty-IFNULL((Select IFNULL(Sum(sgmd.do_pro_qty),0) from gr_do as sgmd where sgmd.activestatus=1 and sgmd.sc_detserialnumber_fk=smp.sc_detserialnumber and sgmd.po_detserialnumber_fk=smp.po_detserialnumber),0)),0) from po_map_sc as psc where psc.activestatus=1 and psc.sc_detserialnumber=smp.sc_detserialnumber and psc.po_detserialnumber=smp.po_detserialnumber),0) as balance_to_deliver from po_map_sc as smp where smp.activestatus=1 and smp.sc_detserialnumber='$row2->sc_detserialnumber' and smp.po_detserialnumber='$row2->po_detserialnumber' GROUP BY smp.sc_detserialnumber HAVING on_stock > 0 LIMIT 1";
                $s3 = mysql_query($query3) or die(mysql_error());
                if ($row3 = mysql_fetch_object($s3)) {
                    $on_stock = (int) $row3->on_stock;
                    $balance_to_deliver = (int) $row3->balance_to_deliver;

                    if ($balance_to_deliver <= $on_stock) {
                        $reserved_qty = $balance_to_deliver;
                    } else {
                        $reserved_qty = $on_stock;
                    }
                }
                $balance_to_sale = $balance_to_sale + $reserved_qty;
            }

            if ($row->customer_serialnumber_fk != 0) {
                $temp->reserved_qty = $balance_to_sale;
            } else {
                $temp->reserved_qty = 0;
            }

            $ret[] = $temp;
        }
        mysql_free_result($s);
        return $ret;
    }

    public function getCompletedSCHeader() {
        $ret = array();
        $query = "Select sh.serialnumber,concat(sh.no_of_days,' Days') as no_of_days,sh.sales_con_code,sh.sales_con_date,sh.sales_con_status,sh.sc_remarks,sh.current_rate,sh.sc_gst,cur.currency_name,IFNULL(cus.client_name,'Alliance Enterprices') as customer from sales_conformation_header as sh LEFT JOIN client_header as cus ON cus.serialnumber=sh.customer_serialnumber_fk LEFT JOIN currency_header as cur ON cur.serialnumber=sh.currency_serialnumber_fk where sh.activestatus=1 and sh.serialnumber IN(Select sd.sc_serialnumber_fk from sales_conformation_details as sd where sd.pro_status='FULLY SATISFIED')";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) {
            $temp = new VoSCHeaderSearch();
            $temp->sc_serialnumber = (int) $row->serialnumber;
            $temp->sales_con_code = $row->sales_con_code;
            $temp->sales_con_date = date("d-m-Y", strtotime($row->sales_con_date));
            $temp->sales_con_status = $row->sales_con_status;
            $temp->no_of_days = $row->no_of_days;
            $temp->sc_remarks = $row->sc_remarks;
            $temp->customer = $row->customer;
            $temp->currency_name = $row->currency_name;
            $temp->current_rate = (double) $row->current_rate;
            $temp->sc_gst = (double) $row->sc_gst;
            $ret[] = $temp;
        }
        mysql_free_result($s);
        return $ret;
    }

    public function getCompletedSCDetails($sc_serialnumber) {
        $ret = array();
        $query = "Select sh.customer_serialnumber_fk,sh.sales_con_code,if(sd.sales_type='Indent Sales' && sh.customer_serialnumber_fk!=0,1,0) as sales_type,sd.sc_serialnumber_fk,sd.detserialnumber,sd.pro_serialnumber_fk,pro.pro_name,pro.part_number,pro.pro_description,sd.pro_qty,sd.price_per_unit,round(( sd.pro_qty * sd.price_per_unit),4) as pro_total_price,IFNULL((Select Sum(dd.delivered_qty) from delivery_order_pro_details as dd where dd.activestatus=1 and dd.sc_detserialnumber_fk=sd.detserialnumber),0) as delivered_qty,(sd.pro_qty - IFNULL((Select Sum(dd.delivered_qty) from delivery_order_pro_details as dd where dd.activestatus=1 and dd.sc_detserialnumber_fk=sd.detserialnumber),0)) as balance_qty
	    from sales_conformation_details as sd LEFT JOIN sales_conformation_header as sh ON sh.serialnumber=sd.sc_serialnumber_fk LEFT JOIN product_header as pro ON pro.serialnumber=sd.pro_serialnumber_fk where sd.activestatus=1 and sd.sc_serialnumber_fk='$sc_serialnumber' and sd.pro_qty>0 and sd.pro_status='FULLY SATISFIED'";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) {
            $temp = new VoSCDetailSearch();
            $temp->sales_con_code = $row->sales_con_code;
            $temp->sales_type = (int) $row->sales_type;
            $temp->sc_serialnumber_fk = (int) $row->sc_serialnumber_fk;
            $temp->sc_detserialnumber = (int) $row->detserialnumber;
            $temp->pro_serialnumber_fk = (int) $row->pro_serialnumber_fk;
            $temp->pro_name = $row->pro_name;
            $temp->part_number = $row->part_number;
            $temp->pro_description = $row->pro_description;
            $temp->pro_qty = (int) $row->pro_qty;
            $temp->price_per_unit = (double) $row->price_per_unit;
            $temp->pro_total_price = (double) $row->pro_total_price;
            $temp->delivered_qty = (int) $row->delivered_qty;
            $temp->balance_qty = (int) $row->balance_qty;
            $temp->cancelled_qty = (int) $row->balance_qty;

            $balance_to_sale = 0;

            $query2 = "Select pms.map_serialnumber,pms.po_detserialnumber,pms.sc_detserialnumber,IFNULL(Sum(pms.allocated_qty-(Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.po_detserialnumber_fk=pms.po_detserialnumber and gmd.sc_detserialnumber_fk=pms.sc_detserialnumber)),0) as balance_qty,(Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.po_detserialnumber_fk=pms.po_detserialnumber and gmd.sc_detserialnumber_fk=pms.sc_detserialnumber) as delivered_qty from po_map_sc as pms LEFT JOIN po_details as pd ON pd.activestatus=1 and pd.po_detserialnumber=pms.po_detserialnumber where pms.activestatus=1 and pms.sc_detserialnumber='$row->detserialnumber' GROUP BY pms.po_detserialnumber HAVING balance_qty>0 ORDER BY pms.map_serialnumber";
            $s2 = mysql_query($query2) or die(mysql_error());
            while ($row2 = mysql_fetch_object($s2)) {
                $reserved_qty = 0;

                $query3 = "Select (IFNULL((Select IFNULL(Sum(gd.received_qty-(IFNULL((Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.grn_detserialnumber_fk=gd.grn_detserialnumber),0))),0) from grn_detail as gd where gd.activestatus=1 and gd.po_detserialnumber_fk=smp.po_detserialnumber),0) -  IFNULL((Select IFNULL(Sum(pms.allocated_qty-(IFNULL((Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.sc_detserialnumber_fk=pms.sc_detserialnumber and gmd.po_detserialnumber_fk=pms.po_detserialnumber),0))),0) from po_map_sc as pms where pms.activestatus=1 and pms.po_detserialnumber=smp.po_detserialnumber and pms.self_sc=0 and pms.map_serialnumber<smp.map_serialnumber and pms.sc_detserialnumber IN(Select spms.sc_detserialnumber from po_map_sc as spms where spms.po_detserialnumber=smp.po_detserialnumber and spms.activestatus=1 and spms.self_sc=0 and (spms.sc_type=1 or spms.sc_type=3 or(spms.sc_type=2 and spms.map_serialnumber < (Select map_serialnumber from po_map_sc where activestatus=1 
				and self_sc=0 and sc_type=1 and po_detserialnumber=smp.po_detserialnumber and map_serialnumber>spms.map_serialnumber and map_serialnumber<=smp.map_serialnumber LIMIT 1))) and spms.map_serialnumber<smp.map_serialnumber)),0)) as on_stock,IFNULL((Select IFNULL(Sum(psc.allocated_qty-IFNULL((Select IFNULL(Sum(sgmd.do_pro_qty),0) from gr_do as sgmd where sgmd.activestatus=1 and sgmd.sc_detserialnumber_fk=smp.sc_detserialnumber and sgmd.po_detserialnumber_fk=smp.po_detserialnumber),0)),0) from po_map_sc as psc where psc.activestatus=1 and psc.sc_detserialnumber=smp.sc_detserialnumber and psc.po_detserialnumber=smp.po_detserialnumber),0) as balance_to_deliver from po_map_sc as smp where smp.activestatus=1 and smp.sc_detserialnumber='$row2->sc_detserialnumber' and smp.po_detserialnumber='$row2->po_detserialnumber' GROUP BY smp.sc_detserialnumber HAVING on_stock > 0 LIMIT 1";
                $s3 = mysql_query($query3) or die(mysql_error());
                if ($row3 = mysql_fetch_object($s3)) {
                    $on_stock = (int) $row3->on_stock;
                    $balance_to_deliver = (int) $row3->balance_to_deliver;

                    if ($balance_to_deliver <= $on_stock) {
                        $reserved_qty = $balance_to_deliver;
                    } else {
                        $reserved_qty = $on_stock;
                    }
                }
                $balance_to_sale = $balance_to_sale + $reserved_qty;
            }

            if ($row->customer_serialnumber_fk != 0) {
                $temp->reserved_qty = $balance_to_sale;
            } else {
                $temp->reserved_qty = 0;
            }

            $ret[] = $temp;
        }
        mysql_free_result($s);
        return $ret;
    }

    public function getOutstandingSCHeader() {
        $ret = array();
        $query = "Select sh.serialnumber,concat(sh.no_of_days,' Days') as no_of_days,sh.sales_con_code,sh.sales_con_date,sh.sales_con_status,sh.sc_remarks,sh.current_rate,sh.sc_gst,cur.currency_name,IFNULL(cus.client_name,'Alliance Enterprices') as customer from sales_conformation_header as sh LEFT JOIN client_header as cus ON cus.serialnumber=sh.customer_serialnumber_fk LEFT JOIN currency_header as cur ON cur.serialnumber=sh.currency_serialnumber_fk where sh.activestatus=1 and sh.customer_serialnumber_fk!=0 and sh.serialnumber IN(Select sd.sc_serialnumber_fk from sales_conformation_details as sd 
	   where sd.activestatus=1 and IFNULL((Select Sum(spms.allocated_qty) from po_map_sc as spms where spms.activestatus=1 and spms.sc_detserialnumber=sd.detserialnumber),0) and sd.pro_status!='FULLY SATISFIED')>0";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) {
            $temp = new VoSCHeaderSearch();
            $temp->sc_serialnumber = (int) $row->serialnumber;
            $temp->sales_con_code = $row->sales_con_code;
            $temp->sales_con_date = date("d-m-Y", strtotime($row->sales_con_date));
            $temp->sales_con_status = $row->sales_con_status;
            $temp->no_of_days = $row->no_of_days;
            $temp->sc_remarks = $row->sc_remarks;
            $temp->customer = $row->customer;
            $temp->currency_name = $row->currency_name;
            $temp->current_rate = (double) $row->current_rate;
            $temp->sc_gst = (double) $row->sc_gst;
            $ret[] = $temp;
        }
        mysql_free_result($s);
        return $ret;
    }

    public function getOutstandingSCDetails($sc_serialnumber) {
        $ret = array();
        $query = "Select sh.customer_serialnumber_fk,sh.sales_con_code,if(sd.sales_type='Indent Sales' && sh.customer_serialnumber_fk!=0,1,0) as sales_type,sd.sc_serialnumber_fk,sd.detserialnumber,sd.pro_serialnumber_fk,pro.pro_name,pro.part_number,pro.pro_description,IFNULL((Select Sum(spms.allocated_qty) from po_map_sc as spms where spms.activestatus=1 and spms.sc_detserialnumber=sd.detserialnumber),0) as pro_qty,sd.price_per_unit,round((IFNULL((Select Sum(spms.allocated_qty) from po_map_sc as spms where spms.activestatus=1 and spms.sc_detserialnumber=sd.detserialnumber),0) * sd.price_per_unit),4) as pro_total_price,IFNULL((Select Sum(dd.delivered_qty) from delivery_order_pro_details as dd where dd.activestatus=1 and dd.sc_detserialnumber_fk=sd.detserialnumber),0) as delivered_qty,(IFNULL((Select Sum(spms.allocated_qty) from po_map_sc as spms where spms.activestatus=1 and spms.sc_detserialnumber=sd.detserialnumber),0) - IFNULL((Select Sum(dd.delivered_qty) from delivery_order_pro_details as dd where dd.activestatus=1 and dd.sc_detserialnumber_fk=sd.detserialnumber),0)) as balance_qty 
	   from sales_conformation_details as sd LEFT JOIN sales_conformation_header as sh ON sh.serialnumber=sd.sc_serialnumber_fk LEFT JOIN product_header as pro ON pro.serialnumber=sd.pro_serialnumber_fk where sd.activestatus=1 and sd.sc_serialnumber_fk='$sc_serialnumber' and sd.pro_status!='FULLY SATISFIED' HAVING pro_qty>0";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) {
            $temp = new VoSCDetailSearch();
            $temp->sales_con_code = $row->sales_con_code;
            $temp->sales_type = (int) $row->sales_type;
            $temp->sc_serialnumber_fk = (int) $row->sc_serialnumber_fk;
            $temp->sc_detserialnumber = (int) $row->detserialnumber;
            $temp->pro_serialnumber_fk = (int) $row->pro_serialnumber_fk;
            $temp->pro_name = $row->pro_name;
            $temp->part_number = $row->part_number;
            $temp->pro_description = $row->pro_description;
            $temp->pro_qty = (int) $row->pro_qty;
            $temp->price_per_unit = (double) $row->price_per_unit;
            $temp->pro_total_price = (double) $row->pro_total_price;
            $temp->delivered_qty = (int) $row->delivered_qty;
            $temp->balance_qty = (int) $row->balance_qty;
            $temp->cancelled_qty = (int) $row->balance_qty;

            $balance_to_sale = 0;

            $query2 = "Select pms.map_serialnumber,pms.po_detserialnumber,pms.sc_detserialnumber,IFNULL(Sum(pms.allocated_qty-(Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.po_detserialnumber_fk=pms.po_detserialnumber and gmd.sc_detserialnumber_fk=pms.sc_detserialnumber)),0) as balance_qty,(Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.po_detserialnumber_fk=pms.po_detserialnumber and gmd.sc_detserialnumber_fk=pms.sc_detserialnumber) as delivered_qty from po_map_sc as pms LEFT JOIN po_details as pd ON pd.activestatus=1 and pd.po_detserialnumber=pms.po_detserialnumber where pms.activestatus=1 and pms.sc_detserialnumber='$row->detserialnumber' GROUP BY pms.po_detserialnumber HAVING balance_qty>0 ORDER BY pms.map_serialnumber";
            $s2 = mysql_query($query2) or die(mysql_error());
            while ($row2 = mysql_fetch_object($s2)) {
                $reserved_qty = 0;

                $query3 = "Select (IFNULL((Select IFNULL(Sum(gd.received_qty-(IFNULL((Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.grn_detserialnumber_fk=gd.grn_detserialnumber),0))),0) from grn_detail as gd where gd.activestatus=1 and gd.po_detserialnumber_fk=smp.po_detserialnumber),0) -  IFNULL((Select IFNULL(Sum(pms.allocated_qty-(IFNULL((Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.sc_detserialnumber_fk=pms.sc_detserialnumber and gmd.po_detserialnumber_fk=pms.po_detserialnumber),0))),0) from po_map_sc as pms where pms.activestatus=1 and pms.po_detserialnumber=smp.po_detserialnumber and pms.self_sc=0 and pms.map_serialnumber<smp.map_serialnumber and pms.sc_detserialnumber IN(Select spms.sc_detserialnumber from po_map_sc as spms where spms.po_detserialnumber=smp.po_detserialnumber and spms.activestatus=1 and spms.self_sc=0 and (spms.sc_type=1 or spms.sc_type=3 or(spms.sc_type=2 and spms.map_serialnumber < (Select map_serialnumber from po_map_sc where activestatus=1 
				and self_sc=0 and sc_type=1 and po_detserialnumber=smp.po_detserialnumber and map_serialnumber>spms.map_serialnumber and map_serialnumber<=smp.map_serialnumber LIMIT 1))) and spms.map_serialnumber<smp.map_serialnumber)),0)) as on_stock,IFNULL((Select IFNULL(Sum(psc.allocated_qty-IFNULL((Select IFNULL(Sum(sgmd.do_pro_qty),0) from gr_do as sgmd where sgmd.activestatus=1 and sgmd.sc_detserialnumber_fk=smp.sc_detserialnumber and sgmd.po_detserialnumber_fk=smp.po_detserialnumber),0)),0) from po_map_sc as psc where psc.activestatus=1 and psc.sc_detserialnumber=smp.sc_detserialnumber and psc.po_detserialnumber=smp.po_detserialnumber),0) as balance_to_deliver from po_map_sc as smp where smp.activestatus=1 and smp.sc_detserialnumber='$row2->sc_detserialnumber' and smp.po_detserialnumber='$row2->po_detserialnumber' GROUP BY smp.sc_detserialnumber HAVING on_stock > 0 LIMIT 1";
                $s3 = mysql_query($query3) or die(mysql_error());
                if ($row3 = mysql_fetch_object($s3)) {
                    $on_stock = (int) $row3->on_stock;
                    $balance_to_deliver = (int) $row3->balance_to_deliver;

                    if ($balance_to_deliver <= $on_stock) {
                        $reserved_qty = $balance_to_deliver;
                    } else {
                        $reserved_qty = $on_stock;
                    }
                }
                $balance_to_sale = $balance_to_sale + $reserved_qty;
            }

            if ($row->customer_serialnumber_fk != 0) {
                $temp->reserved_qty = $balance_to_sale;
            } else {
                $temp->reserved_qty = 0;
            }

            $ret[] = $temp;
        }
        mysql_free_result($s);
        return $ret;
    }

    public function getConformedSCHeader() {
        $ret = array();
        $query = "Select sh.serialnumber,concat(sh.no_of_days,' Days') as no_of_days,sh.sales_con_code,sh.sales_con_date,sh.sales_con_status,sh.sc_remarks,sh.current_rate,sh.sc_gst,cur.currency_name,IFNULL(cus.client_name,'Alliance Enterprices') as customer from sales_conformation_header as sh LEFT JOIN client_header as cus ON cus.serialnumber=sh.customer_serialnumber_fk LEFT JOIN currency_header as cur ON cur.serialnumber=sh.currency_serialnumber_fk where sh.activestatus=1 and sh.sales_con_status!='VOID' and sh.sales_con_status!='DRAFT' and sh.serialnumber IN(Select sd.sc_serialnumber_fk from sales_conformation_details as sd 
	    where sd.activestatus=1 and IFNULL(sd.pro_qty - IFNULL((Select Sum(spms.allocated_qty) from po_map_sc as spms where spms.activestatus=1 and spms.sc_detserialnumber=sd.detserialnumber),0),0)>0)";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) {
            $temp = new VoSCHeaderSearch();
            $temp->sc_serialnumber = (int) $row->serialnumber;
            $temp->sales_con_code = $row->sales_con_code;
            $temp->sales_con_date = date("d-m-Y", strtotime($row->sales_con_date));
            $temp->sales_con_status = $row->sales_con_status;
            $temp->no_of_days = $row->no_of_days;
            $temp->sc_remarks = $row->sc_remarks;
            $temp->customer = $row->customer;
            $temp->currency_name = $row->currency_name;
            $temp->current_rate = (double) $row->current_rate;
            $temp->sc_gst = (double) $row->sc_gst;
            $ret[] = $temp;
        }
        mysql_free_result($s);
        return $ret;
    }

    public function getConformedSCDetails($sc_serialnumber) {
        $ret = array();
        $query = "Select sh.sales_con_code,if(sd.sales_type='Indent Sales' && sh.customer_serialnumber_fk!=0,1,0) as sales_type,sd.sc_serialnumber_fk,sd.detserialnumber,sd.pro_serialnumber_fk,pro.pro_name,pro.part_number,pro.pro_description,(sd.pro_qty - IFNULL((Select Sum(spms.allocated_qty) from po_map_sc as spms where spms.activestatus=1 and spms.sc_detserialnumber=sd.detserialnumber),0)) as pro_qty,sd.price_per_unit,round(((sd.pro_qty - IFNULL((Select Sum(spms.allocated_qty) from po_map_sc as spms where spms.activestatus=1 and spms.sc_detserialnumber=sd.detserialnumber),0)) * sd.price_per_unit),4) as pro_total_price 
	    from sales_conformation_details as sd LEFT JOIN sales_conformation_header as sh ON sh.serialnumber=sd.sc_serialnumber_fk LEFT JOIN product_header as pro ON pro.serialnumber=sd.pro_serialnumber_fk where sd.activestatus=1 and sd.sc_serialnumber_fk='$sc_serialnumber' HAVING pro_qty>0";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) {
            $temp = new VoSCDetailSearch();
            $temp->sales_con_code = $row->sales_con_code;
            $temp->sales_type = (int) $row->sales_type;
            $temp->sc_serialnumber_fk = (int) $row->sc_serialnumber_fk;
            $temp->sc_detserialnumber = (int) $row->detserialnumber;
            $temp->pro_serialnumber_fk = (int) $row->pro_serialnumber_fk;
            $temp->pro_qty = (int) $row->pro_qty;
            $temp->delivered_qty = 0;
            $temp->balance_qty = (int) $row->pro_qty;
            $temp->cancelled_qty = (int) $row->pro_qty;
            $temp->price_per_unit = (double) $row->price_per_unit;
            $temp->pro_total_price = (double) $row->pro_total_price;
            $temp->pro_name = $row->pro_name;
            $temp->part_number = $row->part_number;
            $temp->pro_description = $row->pro_description;
            $temp->reserved_qty = 0;
            $ret[] = $temp;
        }
        mysql_free_result($s);
        return $ret;
    }

}

class VoSCHeaderSearch {

    var $sc_serialnumber;
    var $sales_con_code;
    var $sales_con_date;
    var $sales_con_status;
    var $sc_remarks;
    var $sc_gst;
    var $no_of_days;
    var $current_rate;
    var $customer;
    var $currency_name;

}

class VoDoLinksSearch {

    var $delivery_order_code;
    var $delivered_qty;
    var $pro_name;
    var $part_number;
    var $pro_description;
    var $price_per_unit;
    var $pro_total_price;

}

class VoSCDetailSearch {

    var $sc_detserialnumber;
    var $sc_serialnumber_fk;
    var $sales_con_code;
    var $sales_type;
    var $pro_serialnumber_fk;
    var $pro_qty;
    var $delivered_qty;
    var $balance_qty;
    var $cancelled_qty;
    var $price_per_unit;
    var $pro_total_price;
    var $pro_name;
    var $part_number;
    var $pro_description;
    var $reserved_qty;

}

class VoSalesConformation {

    var $serialnumber;
    var $quot_det_sc_serialnumber;
    var $quot_detserialnumber_fk;
    var $quotation_serialnumber_fk;
    var $quotation_code;
    var $quot_sc_serialnumber_fk;
    var $quot_pro_serialnumber_fk;
    var $quot_pro_qty;
    var $customer_serialnumber_fk;
    var $client_name;
    var $total_amount;
    var $total_qty;
    var $sc_term;
    var $sc_gst;
    var $currency_serialnumber_fk;
    var $current_rate;
    var $sales_con_status;
    var $sales_con_date;
    var $no_of_days;
    var $expected_datetime;
    var $sc_remarks;
    var $detserialnumber;
    var $sc_serialnumber_fk;
    var $pro_serialnumber_fk;
    var $pro_detserialnumber;
    var $base_currency_detserialnumber_fk;
    var $pro_name;
    var $pro_description;
    var $part_number;
    var $stamp_number;
    var $model_no;
    var $default_price;
    var $sgd_listprice;
    var $price_list;
    var $pro_qty;
    var $price_per_unit;
    var $pro_total_price;
    var $pro_status;
    var $delivery_loc;
    var $warehouse_serialnumber_fk;
    var $sales_type;
    var $cre_userid_fk;
    var $cre_datetime;
    var $activestatus;

}

class VoCancelledSc {

    var $serialnumber;
    var $sc_detserialnumber_fk;
    var $cancelled_qty;
    var $cre_userid_fk;
    var $cre_datetime;
    var $activestatus;

}

// $e = new SCSearch();
// $V = new VoCancelledSc();
// $V->sc_detserialnumber_fk = 2;
// $V->cancelled_qty = 5;
// $V->cre_userid_fk = 1;
// $V->cre_datetime = 1;
// $e->cancelScItem($V)
?>